/*
 * Copyright 2010 Assert Developments
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * 	http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package be.ad.coffeebreak.dao;

import be.ad.coffeebreak.domain.User;
import be.ad.coffeebreak.exceptions.DaoException;

import javax.sql.DataSource;
import java.sql.*;

/**
 * A data access object for managing users.
 *
 * @author Stefan Bangels
 */
public class UsersDao {

    private static final String SQL_FIND_USER_BY_EMAIL_AND_PASSWORD = "" +
            "select " +
            "  id, email, name, description, country, language, timezone, theme, " +
            "  creation_date, modification_date " +
            "from users " +
            "where email = ? and password = ? ";

    private static final String SQL_FIND_USER_ID_BY_EMAIL = "" +
            "select id " +
            "from users " +
            "where email = ? ";

    private static final String SQL_GENERATE_USER_ID = "" +
            "select nextval('seq_user_id') as id";

    private static final String SQL_INSERT_USER = "" +
            "insert into users " +
            "(id, email, name, description, country, language, timezone, theme, " +
            " creation_date, modification_date) " +
            "values " +
            "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";

    private static final String SQL_UPDATE_USER = "" +
            "update users " +
            "set email = ?, name = ?, description = ?, country = ?, language = ?, " +
            "    timezone = ?, theme = ?, modification_date = ? " +
            "where id = ? ";

    private static final String SQL_UPDATE_PASSWORD = "" +
            "update users " +
            "set password = ?, modification_date = ? " +
            "where email = ? ";

    private static final String SQL_DELETE_USER = "" +
            "delete from users " +
            "where email = ? ";

    protected final DataSource dataSource;

    /**
     * Constructor providing a data source.
     *
     * @param dataSource the data source
     */
    public UsersDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * Get the data source.
     *
     * @return the data source
     */
    public DataSource getDataSource() {
        return dataSource;
    }

    /**
     * Find a user by its email address and password.  When no match is found, null is returned.
     *
     * @param email    the email address
     * @param password the password
     * @return the user that matches the email address and password, or null when no match was found
     * @throws DaoException when there was an unexpected problem accessing the user
     */
    public User findUser(String email, String password) {
        try {
            Connection connection = getDataSource().getConnection();
            try {
                // Find the user by email and password
                PreparedStatement statement = connection.prepareStatement(SQL_FIND_USER_BY_EMAIL_AND_PASSWORD);
                try {
                    statement.setString(1, email);
                    statement.setString(2, password);
                    ResultSet resultSet = statement.executeQuery();
                    if (resultSet.next()) {
                        return mapUser(resultSet);
                    }
                    return null;
                } finally {
                    statement.close();
                }
            } finally {
                connection.close();
            }
        } catch (Throwable t) {
            throw new DaoException(t);
        }
    }

    /**
     * Find the id of a user by its email address.  When no match is found, null is returned.
     *
     * @param email the email address
     * @return the id of the user that matches the email address, or null when no match was found
     * @throws DaoException when there was an unexpected problem accessing the user
     */
    public Long findUserId(String email) {
        try {
            Connection connection = getDataSource().getConnection();
            try {
                // Find the user by email and password
                PreparedStatement statement = connection.prepareStatement(SQL_FIND_USER_ID_BY_EMAIL);
                try {
                    statement.setString(1, email);
                    ResultSet resultSet = statement.executeQuery();
                    if (resultSet.next()) {
                        return resultSet.getLong("id");
                    }
                    return null;
                } finally {
                    statement.close();
                }
            } finally {
                connection.close();
            }
        } catch (Throwable t) {
            throw new DaoException(t);
        }
    }

    /**
     * Create a user.  After creating the user, the new user id and creation date will be set on the user and returned
     * to the caller.
     *
     * @param user the user
     * @return the user (set with the new user id and creation date)
     * @throws DaoException when there was an unexpected problem inserting the user
     */
    public User insertUser(User user) {
        try {
            Connection connection = getDataSource().getConnection();
            try {
                // Generate the new user id
                Long id;
                PreparedStatement statement = connection.prepareStatement(SQL_GENERATE_USER_ID);
                try {
                    ResultSet rs = statement.executeQuery();
                    if (!rs.next()) {
                        throw new DaoException("error while generating user id");
                    }
                    id = rs.getLong("id");
                } finally {
                    statement.close();
                }

                // Get current date
                Timestamp currentDate = new Timestamp(System.currentTimeMillis());

                // Insert the user
                statement = connection.prepareStatement(SQL_INSERT_USER);
                try {
                    statement.setLong(1, id);
                    statement.setString(2, user.getEmail());
                    statement.setString(3, user.getName());
                    statement.setString(4, user.getDescription());
                    statement.setString(5, user.getCountry());
                    statement.setString(6, user.getLanguage());
                    statement.setString(7, user.getTimeZone());
                    statement.setString(8, user.getTheme());
                    statement.setTimestamp(9, currentDate);
                    statement.setTimestamp(10, currentDate);
                    statement.executeUpdate();
                } finally {
                    statement.close();
                }

                // Set the new user id, creation date and modification date
                user.setId(id);
                user.setCreationDate(currentDate);
                user.setModificationDate(currentDate);

                return user;
            } finally {
                connection.close();
            }
        } catch (Throwable t) {
            throw new DaoException(t);
        }
    }

    /**
     * Update a user.  After updating the user, the new modification date will be set on the user and returned to the
     * caller.
     *
     * @param user the user
     * @return the user
     * @throws DaoException when there was an unexpected problem updating the user
     */
    public User updateUser(User user) {
        try {
            Connection connection = getDataSource().getConnection();
            try {
                // Get current date
                Timestamp currentDate = new Timestamp(System.currentTimeMillis());

                // Update the user
                PreparedStatement statement = connection.prepareStatement(SQL_UPDATE_USER);
                try {
                    statement.setString(1, user.getEmail());
                    statement.setString(2, user.getName());
                    statement.setString(3, user.getDescription());
                    statement.setString(4, user.getCountry());
                    statement.setString(5, user.getLanguage());
                    statement.setString(6, user.getTimeZone());
                    statement.setString(7, user.getTheme());
                    statement.setTimestamp(8, currentDate);
                    statement.setLong(9, user.getId());
                    statement.executeUpdate();
                } finally {
                    statement.close();
                }

                // Set the modification date
                user.setModificationDate(currentDate);

                return user;
            } finally {
                connection.close();
            }
        } catch (Throwable t) {
            throw new DaoException(t);
        }
    }

    /**
     * Change the password of the user that owns the provided email address.
     *
     * @param email       the email address
     * @param newPassword the new password
     * @throws DaoException when there was an unexpected problem changing the password
     */
    public void changePassword(String email, String newPassword) {
        try {
            Connection connection = getDataSource().getConnection();
            try {
                // Change the password
                PreparedStatement statement = connection.prepareStatement(SQL_UPDATE_PASSWORD);
                try {
                    statement.setString(1, newPassword);
                    statement.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
                    statement.setString(3, email);
                    statement.executeUpdate();
                } finally {
                    statement.close();
                }
            } finally {
                connection.close();
            }
        } catch (Throwable t) {
            throw new DaoException(t);
        }
    }

    /**
     * Delete the user that owns the provided email address.
     *
     * @param email the email address
     * @throws DaoException when there was an unexpected problem deleting the user
     */
    public void deleteUser(String email) {
        try {
            Connection connection = getDataSource().getConnection();
            try {
                // Delete the user
                PreparedStatement statement = connection.prepareStatement(SQL_DELETE_USER);
                try {
                    statement.setString(1, email);
                    statement.executeUpdate();
                } finally {
                    statement.close();
                }
            } finally {
                connection.close();
            }
        } catch (Throwable t) {
            throw new DaoException(t);
        }
    }

    /**
     * Map a user by a result set.
     *
     * @param resultSet the result set
     * @return the user
     * @throws java.sql.SQLException when there was an unexpected problem mapping the result set to a user
     */
    protected User mapUser(ResultSet resultSet) throws SQLException {
        User user = new User();
        user.setId(resultSet.getLong("id"));
        user.setEmail(resultSet.getString("email"));
        user.setName(resultSet.getString("name"));
        user.setDescription(resultSet.getString("description"));
        user.setCountry(resultSet.getString("country"));
        user.setLanguage(resultSet.getString("language"));
        user.setTimeZone(resultSet.getString("timezone"));
        user.setTheme(resultSet.getString("theme"));
        user.setCreationDate(resultSet.getTimestamp("creation_date"));
        user.setModificationDate(resultSet.getTimestamp("modification_date"));
        return user;
    }

}
